﻿Imports System.Data.SqlClient

Module mConnect
    ' Connection String
    Public conn As String = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & Application.StartupPath & "\Base\ArtesianWell.accdb;Persist Security Info=False;"
   
    Public Function SQL_exec(ByVal sqlstr As String) As DataTable
        Dim objConn As New OleDb.OleDbConnection
        Dim Command As OleDb.OleDbDataAdapter
        Dim MyTable As New DataTable


        objConn = New OleDb.OleDbConnection(conn)
        objConn.Open()

        Command = New OleDb.OleDbDataAdapter(sqlstr, objConn)
        Command.Fill(MyTable)

        Command = Nothing
        objConn.Close()
        objConn = Nothing

        Return MyTable

    End Function

    Public Function SQL_cmd(ByVal sqlstr As String) As Boolean ' insert update delete

        Dim objConn As New OleDb.OleDbConnection(conn)
        Dim cmd As New OleDb.OleDbCommand()
        Try
            'เปิด connection
            objConn.Open()
            'ส่ง connection ให้ cmd
            cmd.Connection = objConn
            cmd.CommandText = sqlstr
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show("ไม่สามารถทำงานได้เนื่องจาก " & ex.Message.ToString)
            Return False
        Finally
            'ปิด connetion ทุกครั้ง
            objConn.Close()
            'และทำลายตัวแปรทิ้ง
            cmd.Dispose()
            objConn.Dispose()
        End Try
    End Function

    Public Function SQL_result(ByVal rsData As DataTable, ByVal Field As Object, ByVal i As Integer)
        SQL_result = rsData.Rows(i).Item(Field)
    End Function

    Public Function GenId(ByVal tableName As String, ByVal field As String) As String
        Dim sql As String = ""
        sql = " select count(cast(" & field & " as int))+1 as genid from " & tableName & ""
        Dim dt As Data.DataTable
        dt = SQL_exec(sql)
        GenId = SQL_result(dt, "genid", 0)
    End Function
End Module
